# -*- coding: utf-8 -*-
import json
from flask_cors import CORS
from flask import Flask,render_template,request,Response
from threading import Timer
from  datetime import datetime 
import uuid

"""数据库"""
# smtplib 用于邮件的发信动作
import smtplib
from email.mime.text import MIMEText
# email 用于构建邮件内容
from email.header import Header

#import MySQLdb
#from DBUtils.PooledDB import PooledDB

import pymysql
import threading
from DBUtils.PooledDB import PooledDB, SharedDBConnection
pool = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=6,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=5,  # 链接池中最多闲置的链接，0和None不限制
    maxshared=3,  # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
    host='ip',
    port=3306,
    user='用户名',
    password='密码',
    database='数据库名称',
    charset='utf8'
)
format_pattern = "%Y-%m-%d %H:%M:%S"


app = Flask(__name__)
CORS(app, supports_credentials=True)

@app.route('/')
def index(): #一访问127.0.0.1:5000就会返回index模板中的链接”跳转到搜索页面”
    return render_template('index.html')
#*********************************************

"""提醒邮箱"""
def sendemail(to_addr,t,text):
   
    # 用于构建邮件头
     # 发信方的信息：发信邮箱，QQ 邮箱授权码
    from_addr = '发信邮箱'
    password = '邮箱授权码'
    # 发信服务器
    smtp_server = 'smtp.126.com'
    """收信方邮箱"""
    #to_addr = 'school17801157815@163.com'
    
    """标题"""
    head="任务提醒"
    """正文"""
    text=t+"，"+text+"！"
     
    # 邮箱正文内容，第一个参数为内容，第二个参数为格式(plain 为纯文本)，第三个参数为编码
    msg = MIMEText(text,'plain','utf-8')
    # 邮件头信息
    msg['From'] = Header(from_addr)
    msg['To'] = Header(to_addr)
    msg['Subject'] = Header(head)
    # 开启发信服务，这里使用的是加密传输
    server=smtplib.SMTP_SSL(smtp_server)
    server.connect(smtp_server,465)
    # 登录发信邮箱
    server.login(from_addr, password)
    # 发送邮件
    server.sendmail(from_addr, to_addr, msg.as_string())
    # 关闭服务器
    server.quit()
    
    #return Response(json.dumps(str,ensure_ascii=False), mimetype='application/json')

"""添加新任务"""
@app.route('/addTask', methods=['POST', 'GET'])  # 添加路由
def addTask():
    text = request.args.get("text")
    time = request.args.get("time")
    email = request.args.get("email")    
    id = str(uuid.uuid1()).replace("-","")
    createtime=datetime.now().strftime('%Y-%m-%d %H:%M:%S')#现在s    
    result =""
    
    conn = pool.connection()  #以后每次需要数据库连接就是用connection（）函数获取连接就好了
    cur=conn.cursor()
    cur.execute("set names utf8")
    sql_insert ='insert into task(id,text,time,email,createtime) values("'+id+'","'+text+'","'+time+'","'+email+'","'+createtime+'")'
    try:
        cur.execute(sql_insert)
        #提交
        conn.commit()
        result="ok"
    except Exception as e:
        #错误回滚
        conn.rollback() 
        result="fail"
    finally:
        conn.close()
       
    return Response(json.dumps(result,ensure_ascii=False), mimetype='application/json')


"""删除任务"""
@app.route('/deleteTask', methods=['POST', 'GET'])  # 添加路由
def deleteTask():
    id = request.args.get("id")
    result =""
    conn = pool.connection()  #以后每次需要数据库连接就是用connection（）函数获取连接就好了
    cur=conn.cursor()
    cur.execute("set names utf8")
    sql = 'delete from task where id="'+id+'"'
    try:
        cur.execute(sql)
        #提交
        conn.commit()
        result="ok"
    except Exception as e:
        #错误回滚
        conn.rollback() 
        result="fail"
    finally:
        conn.close()
       
    return Response(json.dumps(result,ensure_ascii=False), mimetype='application/json')



"""查询任务"""
@app.route('/findTask', methods=['POST', 'GET'])  # 添加路由
def findTask():
    id = request.args.get("id")
    slist = []
    conn = pool.connection()  #以后每次需要数据库连接就是用connection（）函数获取连接就好了
    cur=conn.cursor()
    cur.execute("set names utf8")
    sql = 'select * from task where id="'+id+'"'
    try:
        cur.execute(sql)     #执行sql语句
        results = cur.fetchall()    #获取查询的所有记录
        #遍历结果
        for row in results :
            Arr = {
                "id":str(row[0]),
                "text":str(row[1]),
                "time":str(row[2]),
                "email":str(row[3]),
                "createtime":str(row[4])

               }
            slist.append(Arr)
        
    except Exception as e:
        raise e
    finally:
        conn.close()    #关闭连接
        
    return Response(json.dumps(slist,ensure_ascii=False), mimetype='application/json')

"""查询"""
@app.route('/find', methods=['POST', 'GET'])  # 添加路由
def find():
   
    slist = []
    conn = pool.connection()  #以后每次需要数据库连接就是用connection（）函数获取连接就好了
    cur=conn.cursor()
    cur.execute("set names utf8")
    sql = 'select * from task  order by  createtime '
    try:
        cur.execute(sql)     #执行sql语句
        results = cur.fetchall()    #获取查询的所有记录
        #遍历结果
        for row in results :
            Arr = {
                "id":str(row[0]),
                "text":str(row[1]),
                "time":str(row[2]),
                "email":str(row[3]),
                "createtime":str(row[4])

               }
            slist.append(Arr)
        
    except Exception as e:
        raise e
    finally:
        conn.close()    #关闭连接
     
    return Response(json.dumps(slist,ensure_ascii=False), mimetype='application/json')

def s():
    
    slist = []
    conn = pool.connection()  #以后每次需要数据库连接就是用connection（）函数获取连接就好了
    cur=conn.cursor()
    cur.execute("set names utf8")
    sql = 'select * from task where ispass=0  order by  createtime  '
    try:
        cur.execute(sql)     #执行sql语句
        results = cur.fetchall()    #获取查询的所有记录
        #遍历结果
        for row in results :
            Arr = {
                "id":str(row[0]),
                "text":str(row[1]),
                "time":str(row[2]),
                "email":str(row[3]),
                "createtime":str(row[4])

               }
            slist.append(Arr)
        
    except Exception as e:
        raise e
    finally:
        conn.close()    #关闭连接
    
    
    start=datetime.now().strftime('%Y-%m-%d %H:%M:%S')#现在
    for i in range(0,len(slist)):
        end = slist[i]['time']
    
        difference = (datetime.strptime(end, format_pattern) - datetime.strptime(start, format_pattern))
        if(difference.days>=0):
            if(difference.seconds<80):
                sendemail(slist[i]['email'],slist[i]['time'],slist[i]['text'])
                """0-----1"""
                conn = pool.connection()  #以后每次需要数据库连接就是用connection（）函数获取连接就好了
                cur=conn.cursor()
                cur.execute("set names utf8")
                sql_insert ='UPDATE task set ispass =1 where id ="'+slist[i]['id']+'"'
                try:
                    cur.execute(sql_insert)
                    #提交
                    conn.commit()
                except Exception as e:
                    #错误回滚
                    conn.rollback() 
                finally:
                    conn.close()
               #print("差秒="+str(difference.seconds))
    
    
    
    #每隔86400秒发送一次，每天发送一次
    Timer(60,s).start()
    
if __name__ == '__main__':
    
    """数据库"""
    """连接池"""
    s()
    #app.run(host='172.30.0.16', port=8000)
    app.run(host='127.0.0.1', port=8000)